﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Threading;
using System.Threading.Tasks;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;

namespace prjKQXS_DLL.CSDL
{
    public class clsDatabase
    {
        #region
        //Chuyển dữ liệu từ @Source (csdl nguồn) sang @Destination (csdl đích)
        //Dùng replace (Ctrl + H) để thay thế các tên tương ứng

        public string convertDatabase = @"
USE @Destination		-- CSDL đích
GO

DELETE FROM TLLX2
DELETE FROM LAIXUAT_DL
DELETE FROM DOANHTHU
DELETE FROM tblChuyenDL
DELETE FROM tblVESO_DL
DELETE FROM tblVESO_CB
DELETE FROM tblVESO_CT
DELETE FROM HOPDONG
DELETE FROM DMTINHTRANG
DELETE FROM DMLOAIHOPDONG
DELETE FROM DMLYDOTHANHLY

DELETE FROM TLLX1
DELETE FROM LAIXUAT_CB
DELETE FROM DMCANBO
DELETE FROM DMDONVI
DELETE FROM DMCHUCVU

DELETE FROM KETQUATT
DELETE FROM DMHOIDONG
DELETE FROM DMQUY
DELETE FROM May_Chu

DELETE FROM Bangslloto
DELETE FROM Bangkeloto
DELETE FROM Maynhaploto

USE @Source		-- CSDL nguồn
GO

SET IDENTITY_INSERT @Destination.dbo.DMCANBO OFF
SET IDENTITY_INSERT @Destination.dbo.HOPDONG OFF
SET IDENTITY_INSERT @Destination.dbo.tblVESO_DL OFF
SET IDENTITY_INSERT @Destination.dbo.tblVESO_CB OFF
SET IDENTITY_INSERT @Destination.dbo.KETQUATT OFF
SET IDENTITY_INSERT @Destination.dbo.DMHOIDONG OFF
SET IDENTITY_INSERT @Destination.dbo.DMQUY OFF
SET IDENTITY_INSERT @Destination.dbo.Bangkeloto OFF

--Chèn dữ liệu cho bảng DMDONVI
INSERT INTO @Destination.dbo.DMDONVI (Ma_DT, Ten_DT, Giam_Doc, SDT, FAX, Dia_Chi, isPublic)	
	SELECT Ma_DT, Ten_DT, Giam_Doc, SDT, FAX, Dia_Chi, isPublic FROM @Source.dbo.DMDONVI
GO

--Chèn dữ liệu cho bảng DMCHUCVU
INSERT INTO @Destination.dbo.DMCHUCVU (Ma_DT, Ten_DT, isPublic)
	SELECT Ma_DT, Ten_DT, isPublic FROM @Source.dbo.DMCHUCVU

--Chèn dữ liệu cho bảng DMCANBO
--SET IDENTITY_INSERT @Destination.dbo.HOPDONG OFF
--SET IDENTITY_INSERT @Source.dbo.DMQUY OFF
SET IDENTITY_INSERT @Destination.dbo.DMCANBO ON
INSERT INTO @Destination.dbo.DMCANBO(ID, Ma_DT, Ten_DT, SDT, Ma_DMChucVu, Ma_DMDonVi, Tuyen, Noi_O, MST, CMT, Noi_Cap
		, Ngay_Cap, isBanRong, So_CT, Ngay_TinhLai, Tien_Goc, isKetThuc, SoHD, NgayHD, Ngay_ThanhLy, isPublic)
	SELECT ID, Ma_DT, Ten_DT, SDT, Ma_DMChucVu, Ma_DMDonVi, Tuyen, Noi_O, MST, CMT, Noi_Cap
		, Ngay_Cap, isBanRong, So_CT, Ngay_TinhLai, Tien_Goc, isKetThuc, SoHD, NgayHD, Ngay_ThanhLy, isPublic
	FROM @Source.dbo.DMCANBO
		
	
--Chèn dữ liệu cho bảng LAIXUAT_CB 
--Chèn dữ liệu cho bảng TLLX1

--Chèn dữ liệu cho bảng DMTINHTRANG
INSERT INTO @Destination.dbo.DMTINHTRANG (Ma_DT, Ten_DT, isPublic)
	SELECT	Ma_DT, Ten_DT, isPublic FROM @Source.dbo.DMTINHTRANG

--Chèn dữ liệu cho bảng DMLOAIHOPDONG
INSERT INTO @Destination.dbo.DMLOAIHOPDONG(Ma_DT, Ten_DT, URL, isPublic)
	SELECT Ma_DT, Ten_DT, URL, isPublic FROM @Source.dbo.DMLOAIHOPDONG
	
--Chèn dữ liệu cho bảng DMLYDOTHANHLY
SET IDENTITY_INSERT @Destination.dbo.DMCANBO OFF
SET IDENTITY_INSERT @Destination.dbo.DMLYDOTHANHLY ON
INSERT INTO @Destination.dbo.DMLYDOTHANHLY (ID, Ma_DT, Ten_DT, isPublic)
	SELECT	ID, Ma_DT, Ten_DT, isPublic FROM @Source.dbo.DMLYDOTHANHLY
	
--Chèn dữ liệu cho bảng LAIXUAT_DL 
--Chèn dữ liệu cho bảng TLLX2

--Chèn dữ liệu cho bảng HOPDONG
	--Chèn
	SET IDENTITY_INSERT @Destination.dbo.DMLYDOTHANHLY OFF
	SET IDENTITY_INSERT @Destination.dbo.HOPDONG ON
	INSERT INTO @Destination.dbo.HOPDONG(ID, Ma_DT, Ngay_HD, DMCanBoID, Noi_O, MST, CMT, Noi_Cap, Ngay_Cap, Noi_Ban, Ngay_Ban
			, So_Tien, Ma_DMTinhTrang, Ma_DMLoaiHD, Ngay_ThanhLy, Ly_Do, Ma_DaiLy, Ten_DaiLy, isSua
			, isCapBac, isKetThuc, Ngay_KT, isTT, Tenlanhdao, Ngaynoptien, Sodienthoai, Loaidaily, Sochungtu, isPublic)
		SELECT ID, Ma_DT, Ngay_HD, DMCanBoID, Noi_O, MST, CMT, Noi_Cap, Ngay_Cap, Noi_Ban, Ngay_Ban
			, So_Tien, Ma_DMTinhTrang, Ma_DMLoaiHD, Ngay_ThanhLy, Ly_Do, Ma_DaiLy, Ten_DaiLy, isSua
			, isCapBac, isKetThuc, Ngay_KT, isTT, Tenlanhdao, Ngaynoptien, Sodienthoai, Loaidaily, Sochungtu, isPublic
			
		FROM @Source.dbo.HOPDONG 
	 
	  	  
--Chèn dữ liệu cho bảng tblChuyenDL
	INSERT INTO @Destination.dbo.tblChuyenDL(Ngay, HopDongID, DMCanBoID, MaDLMoi, isPublic)
		SELECT Ngay, HopDongID, DMCanBoID, MaDLMoi, isPublic 
		FROM @Source.dbo.tblChuyenDL


		  	  
--Chèn dữ liệu cho bảng tblVESO_DL
SET IDENTITY_INSERT @Destination.dbo.HOPDONG OFF
SET IDENTITY_INSERT @Destination.dbo.tblVESO_DL ON
INSERT INTO @Destination.dbo.tblVESO_DL(ID, HopDongID, NgayMoThuong, SLVeNhan, SLVeHuy, Giave, Kyhieu, isPublic)	  	  
	SELECT ID, HopDongID, NgayMoThuong, SLVeNhan, SLVeHuy, Giave, Kyhieu, isPublic
	FROM @Source.dbo.tblVESO_DL
		  	  
--Chèn dữ liệu cho bảng tblVESO_CB
SET IDENTITY_INSERT @Destination.dbo.tblVESO_DL OFF
SET IDENTITY_INSERT @Destination.dbo.tblVESO_CB ON
INSERT INTO @Destination.dbo.tblVESO_CB(ID, DMCanBoID, NgayMoThuong, SLVeNhan, SLVeHuy, Giave, Kyhieu, isPublic)	  	  
	SELECT ID, DMCanBoID, NgayMoThuong, SLVeNhan, SLVeHuy, Giave, Kyhieu, isPublic
	FROM @Source.dbo.tblVESO_CB
		  	  
--Chèn dữ liệu cho bảng tblVESO_CT
SET IDENTITY_INSERT @Destination.dbo.tblVESO_CB OFF
SET IDENTITY_INSERT @Destination.dbo.tblVESO_CT ON
INSERT INTO @Destination.dbo.tblVESO_CT(ID, Ma_DMDonVi, NgayMoThuong, SLVeNhan, SLVeHuy, Giave, Kyhieu, isPublic)	  	  
	SELECT ID, Ma_DMDonVi, NgayMoThuong, SLVeNhan, SLVeHuy, Giave, Kyhieu, isPublic
	FROM @Source.dbo.tblVESO_CT  	  
	  	  
--Chèn dữ liệu cho bảng DOANHTHU
INSERT INTO @Destination.dbo.DOANHTHU(HopDongID, Truyen_Thong, Boc, LoTo234, CapSo, Ngay, TID, DoanhThu, isBanRong, isPublic)
	SELECT HopDongID, Truyen_Thong, Boc, LoTo234, CapSo, Ngay, TID, DoanhThu, isBanRong, isPublic
	FROM @Source.dbo.DOANHTHU
		

----Chèn dữ liệu cho bảng KETQUATT
SET IDENTITY_INSERT @Destination.dbo.tblVESO_CT OFF
SET IDENTITY_INSERT @Destination.dbo.KETQUATT ON
INSERT INTO @Destination.dbo.KETQUATT(ID, Loaive, Kyhieu, Giave, Pchutich, Congty, Sotuphap, Congantinh, MattranTQ, ThanhtraNN, Thuky, Ngaymothuong, Sotaichinh)
	SELECT ID, Loaive, Kyhieu, Giave, Pchutich, Congty, Sotuphap, Congantinh, MattranTQ, ThanhtraNN, Thuky, Ngaymothuong, Sotaichinh
	FROM @Source.dbo.KETQUATT


----Chèn dữ liệu cho bảng DMHOIDONG
SET IDENTITY_INSERT @Destination.dbo.KETQUATT OFF
SET IDENTITY_INSERT @Destination.dbo.DMHOIDONG ON
INSERT INTO @Destination.dbo.DMHOIDONG(ID, Mahoidong, Tenhoidong, Chucvu, SDT, isPublic)
	SELECT ID, Mahoidong, Tenhoidong, Chucvu, SDT, isPublic	FROM @Source.dbo.DMHOIDONG

----Chèn dữ liệu cho bảng DMQUY
SET IDENTITY_INSERT @Destination.dbo.DMHOIDONG OFF
SET IDENTITY_INSERT @Destination.dbo.DMQUY ON
INSERT INTO @Destination.dbo.DMQUY(ID, Ngay,Ngaychungtu,Madv,Sophieuthu,Sophieuchi,Noidung,Sotienthu,Sotienchi,Tonquy)
	SELECT ID, Ngay,Ngaychungtu,Madv,Sophieuthu,Sophieuchi,Noidung,Sotienthu,Sotienchi,Tonquy
	FROM @Source.dbo.DMQUY
	
----Chèn dữ liệu cho bảng May_Chu
INSERT INTO @Destination.dbo.May_Chu(Ten_May, isMayChu, isPublic)
	SELECT Ten_May, isMayChu, isPublic
	FROM @Source.dbo.May_Chu
		
----Chèn dữ liệu cho bảng Bangslloto
INSERT INTO @Destination.dbo.Bangslloto(Ngaymothuong, Madaily, Slve5, Slve10, Slvec10, Slvec20)
	SELECT Ngaymothuong, Madaily, Slve5, Slve10, Slvec10, Slvec20
	FROM @Source.dbo.Bangslloto	
		
----Chèn dữ liệu cho bảng Bangkeloto
SET IDENTITY_INSERT @Destination.dbo.DMQUY OFF
SET IDENTITY_INSERT @Destination.dbo.Bangkeloto ON
INSERT INTO @Destination.dbo.Bangkeloto(ID, Stt, Tenmay, Ngaymothuong, Madonvi, Macanbo, Madaily, Ve5, Ve10, Vec10, Vec20, Thoigian, Khoa)
	SELECT ID, Stt, Tenmay, Ngaymothuong, Madonvi, Macanbo, Madaily, Ve5, Ve10, Vec10, Vec20, Thoigian, Khoa
	FROM @Source.dbo.Bangkeloto
		
----Chèn dữ liệu cho bảng Maynhaploto
INSERT INTO @Destination.dbo.Maynhaploto(Ngaymothuong, Tenmay, Madaily)
	SELECT Ngaymothuong, Tenmay, Madaily
	FROM @Source.dbo.Maynhaploto";
        #endregion

        private SqlConnection _conn = new SqlConnection(BienTc.connString);
        private Server _sv;
        public bool _isCompleted = false;

        /// <summary>
        /// <para>Khi restore không thành công, csdl hiện tại sẽ được khôi phục lại hiện trạng.</para>
        /// <para>Biến này để xác định quá trình khôi phục lại hiện trạng có thành công hay không.</para>
        /// <para>Chỉ dùng trong trường hợp khôi phục không tương thích.</para>
        /// </summary>
        public bool _currentDatabaseIsRestored = false;

        public delegate void MyPercentCompleteEventHandler(int percent);
        public event MyPercentCompleteEventHandler OnMyPercentComplete;
        private int _myPercentComplete = 0;
        private int _restorePercentComplete = 0;
        private int _backupPercentComplete = 0;
        private int _taskNumber = 1;
        private int _setRollBack = 1;
         
        public delegate void OnErrorHandler(Exception e);
        public event OnErrorHandler onOtherException;

        public clsDatabase()
        {
            if (_conn.State != ConnectionState.Open) _conn.Open();
            _sv = new Server(_conn.DataSource);
            _myPercentComplete = 0;
        }

        public DataTable readBackupHeader(string backupFilePath)
        {
            if (_conn.State != ConnectionState.Open) _conn.Open();
            Restore rs = new Restore();
            rs.Devices.AddDevice(backupFilePath, DeviceType.File);
            return rs.ReadBackupHeader(_sv);
        }

        public bool backupKQXS(string backupFilePath)
        {
            bool ok = false;

            try
            {
                if (_conn.State != ConnectionState.Open) _conn.Open();
                Backup bk = new Backup();
                bk.PercentComplete += new PercentCompleteEventHandler(bk_PercentComplete);
                bk.Devices.AddDevice(backupFilePath, DeviceType.File);
                bk.Database = _conn.Database;
                bk.BackupSetName = "KQXS 14.06.04";
                bk.SqlBackup(_sv);
                ok = true;
            }
            catch (Exception ex)
            {
                onOtherException(ex);
            }

            return ok;
        }

        public bool restore_IsCompatible(string filePath)
        {
            bool ok = false;
            _isCompleted = false;
            if (_taskNumber == 1 && _setRollBack == 1) //<=> nếu chỉ chạy restore_IsCompatible
                _otherPercent = 0;

            try
            {
                if (_conn.State != ConnectionState.Open) _conn.Open();
                BackupDeviceItem bdi = new BackupDeviceItem(filePath, DeviceType.File);
                Restore rs = new Restore();
                rs.PercentComplete += new PercentCompleteEventHandler(rs_PercentComplete);
                rs.Complete += new ServerMessageEventHandler(rs_Complete);
                rs.Devices.Add(bdi);
                rs.Database = _conn.Database;
                rs.ReplaceDatabase = true;
                _sv.KillAllProcesses(_conn.Database);
                _sv.Refresh();

                Task task = new Task(() => rs.SqlRestore(_sv));
                task.Start();

                while (!task.IsCanceled && !task.IsCompleted && !task.IsFaulted)
                {
                    if (_taskNumber == 1 && _setRollBack == 1) //<=> nếu chỉ chạy restore_IsCompatible
                    {
                        _otherPercent += _taskNumber;
                        MyPercentComplete(0);
                        Thread.Sleep(_myPercentComplete < 30 ? 3000 : _myPercentComplete * 100);
                    }
                }

                ok = true;
            }
            catch (Exception ex)
            {
                onOtherException(ex);
            }

            if (_taskNumber == 1 && _setRollBack == 1)  //<=> nếu chỉ chạy restore_IsCompatible
                _isCompleted = true;

            return ok;
        }

        public bool restore_IsNotCompatible(string bakupFilePath)
        {
            bool ok = false;
            _isCompleted = false;
            _backupPercentComplete = 0;
            _restorePercentComplete = 0;
            _otherPercent = 0;
            _taskNumber = 3;
            _setRollBack = 1;

            //B1: Backup csdl hiện tại
            if (_conn.State != ConnectionState.Open) _conn.Open();
            string filePath = @"D:\Backup\" + _conn.Database + "_" + DateTime.Now.DayOfWeek + ".bak";
            if (System.IO.File.Exists(filePath)) System.IO.File.Delete(filePath);
            backupKQXS(filePath);
            //clsCommon.MsgBox("Bước 1: Done", 1);


            //B2: Restore file bak với một cái tên khác (KQXS_Temp)
            try
            {
                if (_sv.Databases.Contains("KQXS_Temp"))
                {
                    _sv.KillAllProcesses("KQXS_Temp");
                    if (_conn.State != ConnectionState.Open) _conn.Open();
                    _sv.KillDatabase("KQXS_Temp");
                    _sv.Refresh();
                }

                BackupDeviceItem bdi = new BackupDeviceItem(bakupFilePath, DeviceType.File);
                Restore rs = new Restore();
                rs.PercentComplete += new PercentCompleteEventHandler(rs_PercentComplete);
                rs.Complete += new ServerMessageEventHandler(rs_Complete);
                rs.Devices.Add(bdi);
                rs.Database = "KQXS_Temp";
                rs.ReplaceDatabase = true;
                DataTable dt = rs.ReadFileList(_sv);
                if (!System.IO.Directory.Exists(@"C:\KQXS")) System.IO.Directory.CreateDirectory(@"C:\KQXS"); ;
                rs.RelocateFiles.Add(new RelocateFile(dt.Rows[0][0].ToString().Trim(), @"C:\KQXS\KQXS_Temp.mdf"));
                rs.RelocateFiles.Add(new RelocateFile(dt.Rows[1][0].ToString().Trim(), @"C:\KQXS\KQXS_Temp.ldf"));

                Task task = new Task(() =>rs.SqlRestore(_sv));
                task.Start();

                while (!task.IsCanceled && !task.IsCompleted && !task.IsFaulted)
                {
                    _otherPercent += _taskNumber;
                    MyPercentComplete(0);
                    Thread.Sleep(_myPercentComplete * 100);
                }
            }
            catch (Exception ex)
            {
                onOtherException(ex);
            }
            //clsCommon.MsgBox("Bước 2: Done", 1);


            //B3: Chuyển dữ liệu từ KQXS_Temp sang csdl hiện tại
            string transaction = convertDatabase.Replace("@Source", "KQXS_Temp").Replace("@Destination", _conn.Database);
            try
            {
                Task task = new Task(() => _sv.ConnectionContext.ExecuteNonQuery(transaction));
                task.Start();

                while (!task.IsCanceled && !task.IsCompleted && !task.IsFaulted)
                {
                    _otherPercent += _taskNumber;
                    MyPercentComplete(0);
                    Thread.Sleep(_myPercentComplete * 100);
                }

                task.Wait(); //exception sẽ phát sinh ở đây nếu có
                ok = true;
            }
            catch (AggregateException ex)
            {
                if (ex.InnerException is ExecutionFailureException)
                {
                    ExecutionFailureException efx = ex.InnerException as ExecutionFailureException;
                    if (efx.InnerException is SqlException)
                    {
                        if (((SqlException)efx.InnerException).ErrorCode == -2146232060)
                        {
                            //Nếu quá trình chuyển đổi xảy ra lỗi thì phục hồi lại csdl hiện tại
                            _setRollBack = -1;
                            Task.Factory.StartNew(() =>
                                {
                                    string msg = "Chuyển đổi không thành công vì tệp sao lưu không tương thích với phiên bản hiện tại."
                                        + "\n\n" + efx.Message + "\n----------\n" + efx.InnerException.Message;
                                    clsCommon.MsgBox(msg, 5);
                                });

                            Task task = new Task(() => restore_IsCompatible(filePath));
                            task.Start();

                            while (!task.IsCanceled && !task.IsCompleted && !task.IsFaulted)
                            {
                                _otherPercent -= _taskNumber;
                                MyPercentComplete(0);
                                Thread.Sleep((100 - _myPercentComplete) * 100);
                            }

                            try
                            {
                                task.Wait();
                                _currentDatabaseIsRestored = true;
                            }
                            catch
                            {
                                _currentDatabaseIsRestored = false;
                            }
                        }
                        else onOtherException(efx.InnerException);
                    }
                    else onOtherException(efx.InnerException);

                }
                else onOtherException(ex.InnerException);
            }
            catch (Exception ex)
            {
                onOtherException(ex);
            }
            finally
            {
                _sv.KillAllProcesses("KQXS_Temp");
                if (_conn.State != ConnectionState.Open) _conn.Open();

                Task task = new Task(() => _sv.KillDatabase("KQXS_Temp"));
                task.Start();

                while (!task.IsCanceled && !task.IsCompleted && !task.IsFaulted)
                {
                    if (ok)
                    {
                        _otherPercent += _taskNumber;
                        MyPercentComplete(0);
                        Thread.Sleep(_myPercentComplete * 100);
                    }
                    else
                    {
                        _otherPercent -= _taskNumber;
                        MyPercentComplete(0);
                        Thread.Sleep((100 - _myPercentComplete) * 100);
                    }
                }
                       
                Task.WaitAny();
                if (ok) OnMyPercentComplete(100);
                _sv.Refresh();

                _isCompleted = true;
                _taskNumber = 1;
                _setRollBack = 1;
            }

            return ok;
        }

        object lockObj = new object();
        private void rs_PercentComplete(object sender, PercentCompleteEventArgs e)
        {
            lock (lockObj)
            {
                _restorePercentComplete = e.Percent - _taskNumber;
            }

            MyPercentComplete(_setRollBack == 1 ? 0 : _restorePercentComplete * 2);
        }

        private void rs_Complete(object sender, ServerMessageEventArgs e)
        {
            lock (lockObj)
            {
                _restorePercentComplete = 100;
            }

            MyPercentComplete(_setRollBack == 1 ? 0 : _restorePercentComplete * 2);
        }

        private void bk_PercentComplete(object sender, PercentCompleteEventArgs e)
        {
            lock (lockObj)
            {
                _backupPercentComplete = e.Percent;
            }

            MyPercentComplete(0);
        }

        int _otherPercent = 0;
        private void MyPercentComplete(int otherPercent)
        {
            lock (lockObj)
            {
                if (_taskNumber == 1 && _setRollBack == 1) //<=> nếu chỉ chạy restore_IsCompatible
                {
                    int rsPercent = _restorePercentComplete < 30 ? 0 : _restorePercentComplete - 30;
                    _myPercentComplete = rsPercent + _otherPercent * _setRollBack;
                }
                else
                {
                    if (_setRollBack == 1)
                        _myPercentComplete = (_backupPercentComplete + _restorePercentComplete + _otherPercent * _setRollBack) / _taskNumber;
                    else _myPercentComplete -= 1;
                }
            }

            OnMyPercentComplete(_myPercentComplete < 0 ? 0 : _myPercentComplete > 100 ? 100 : _myPercentComplete);
        }
    }
}
